package com.lc.demo.mapper;

import com.lc.demo.bean.Signin;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import tk.mybatis.mapper.common.BaseMapper;

import java.util.List;
import java.util.Map;

public interface SigninMapper extends BaseMapper<Signin> {


    @Select("select * from signin  where studentid=#{username}")
    List<Signin> findSignListByStudentId(@Param("username") String username);

    @Select("select courseid,weekday,DATE_FORMAT(daytime,'%Y-%m-%d %H:%i:%S'),coursename from signin ")
    List<Signin> getAllSignin();

    @Select("select \n" +
            "(select name from student where username= signin.studentid) name,\n" +
            "studentid,\n" +
            "count(studentid)as count\n" +
            "from\n" +
            "signin\n" +
            "where  daytime < now() and state=0\n" +
            "\n" +
            "GROUP BY studentid\n" +
            "HAVING count>4")
    Map<String, Object> getKeyMonitoringStudent();

    @Select("select * from  signin where daytime < now() and state=0 and studentid=#{id}")
    List<Signin> getStudentSign(@Param("id") String id);

    @Select("select\n" +
            "\n" +
            "t4.id,\n" +
            "t4.departmentname as name,\n" +
            "t5.signcount,\n" +
            "count(t1.studentid) as total,\n" +
            "t5.signcount / count(t1.studentid) as percent\n" +
            "from\n" +
            "signin t1 left join student t2 on t1.studentid=t2.username\n" +
            "LEFT JOIN classgrade t3 ON t2.classid=t3.id \n" +
            "LEFT JOIN t_department t4 ON  t3.departmentid=t4.id \n" +
            "LEFT JOIN\n" +
            "(select\n" +
            "t9.id,\n" +
            "t9.departmentname,\n" +
            "count(t6.studentid) as signcount\n" +
            "from\n" +
            "signin t6 left join student t7 on t6.studentid=t7.username\n" +
            "LEFT JOIN classgrade t8 ON t7.classid=t8.id \n" +
            "LEFT JOIN t_department t9 ON  t8.departmentid=t9.id where t6.state=1\n" +
            "GROUP BY  t9.id\n" +
            ") t5 ON t4.id=t5.id\n" +
            "GROUP BY t4.id\n" +
            "\n" +
            "order by percent desc")
    List<Map<String, Object>> selectDepartmentList();


    @Select("select\n" +
            "\n" +
            "t3.id,\n" +
            "t3.classname as name,\n" +
            "t5.signcount ,\n" +
            "count(t1.studentid) as total,\n" +
            "t5.signcount / count(t1.studentid) as percent\n" +
            "from\n" +
            "signin t1 left join student t2 on t1.studentid=t2.username\n" +
            "LEFT JOIN classgrade t3 ON t2.classid=t3.id  \n" +
            "LEFT JOIN\n" +
            "(\n" +
            "select\n" +
            "t8.id,\n" +
            "t8.classname,\n" +
            "count(t6.studentid) as signcount\n" +
            "\n" +
            "from\n" +
            "signin t6 left join student t7 on t6.studentid=t7.username\n" +
            "LEFT JOIN classgrade t8 ON t7.classid=t8.id \n" +
            " where t6.state=1 and t8.departmentid=#{id}\n" +
            " GROUP BY t8.id\n" +
            ")t5 \n" +
            "ON t3.id=t5.id\n" +
            "\n" +
            "where  t3.departmentid=#{id}\n" +
            "\n" +
            "GROUP BY t3.id\n" +
            "\n" +
            "order by percent desc")
    List<Map<String, Object>> selectClassList(@Param("id") Integer id);



    @Select("select\n" +
            "\n" +
            "t2.username as id,\n" +
            "t2.name as name,\n" +
            "t5.signcount ,\n" +
            "count(t1.studentid) as total,\n" +
            "t5.signcount / count(t1.studentid) as percent\n" +
            "from\n" +
            "signin t1 left join student t2 on t1.studentid=t2.username\n" +
            "\n" +
            "LEFT JOIN\n" +
            "(\n" +
            "select\n" +
            "t4.username as id ,\n" +
            "t4.name,\n" +
            "count(t4.username) as signcount\n" +
            "\n" +
            "from\n" +
            "signin t3 left join student t4 on t3.studentid=t4.username\n" +
            "\n" +
            " where t3.state=1 and t4.classid=#{id}\n" +
            " GROUP BY t4.username\n" +
            ")t5 \n" +
            "ON t2.username=t5.id\n" +
            "\n" +
            "where  t2.classid=#{id}\n" +
            "\n" +
            "GROUP BY t2.username")
    List<Map<String, Object>> selectStudentList(@Param("id") Integer id);
}
